package com.shlh.saas.mapper.canton.buyer;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.shlh.saas.entity.canton.buyer.BaseCantonFairBuyer;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 广交会采购商数据库接口
 */
@Mapper
public interface CantonFairBuyerMapper extends BaseMapper<BaseCantonFairBuyer> {
    
    /**
     * 通过关键词搜索具有电子邮箱的采购商
     * 
     * @param keyword 搜索关键词
     * @param offset 偏移量
     * @param limit 限制数量
     * @return 符合条件的采购商列表
     */
    @Select("<script>" +
            "SELECT id, company_name, contact_person, email, telephone, fax, country, address " +
            "FROM canton_fair_buyers WHERE email IS NOT NULL AND email != '' " +
            "AND (company_name LIKE CONCAT('%',#{keyword},'%') " +
            "OR contact_person LIKE CONCAT('%',#{keyword},'%')) " +
            "ORDER BY id " +
            "LIMIT #{limit} OFFSET #{offset}" +
            "</script>")
    List<BaseCantonFairBuyer> searchBuyersWithEmail(@Param("keyword") String keyword, 
                                                   @Param("offset") int offset, 
                                                   @Param("limit") int limit);
    
    /**
     * 统计通过关键词搜索具有电子邮箱的采购商数量
     * 
     * @param keyword 搜索关键词
     * @return 符合条件的采购商数量
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM canton_fair_buyers WHERE email IS NOT NULL AND email != '' " +
            "AND (company_name LIKE CONCAT('%',#{keyword},'%') " +
            "OR contact_person LIKE CONCAT('%',#{keyword},'%'))" +
            "</script>")
    long countBuyersWithEmail(@Param("keyword") String keyword);
    
    /**
     * 通过关键词搜索具有电话的采购商
     *
     * @param keyword 搜索关键词
     * @param offset 偏移量
     * @param limit 限制数量
     * @return 符合条件的采购商列表
     */
    @Select("SELECT id, company_name, contact_person, email, telephone, fax, country, address " +
            "FROM canton_fair_buyers WHERE telephone IS NOT NULL AND telephone != '' " +
            "AND (company_name LIKE CONCAT('%',#{keyword},'%') " +
            "OR contact_person LIKE CONCAT('%',#{keyword},'%')) " +
            "ORDER BY id " +
            "LIMIT #{limit} OFFSET #{offset}"
    )
    List<BaseCantonFairBuyer> searchBuyersWithPhone(@Param("keyword") String keyword, 
                                                   @Param("offset") int offset, 
                                                   @Param("limit") int limit);
    
    /**
     * 统计通过关键词搜索具有电话的采购商数量
     * 注意：由于表结构问题，直接返回0
     * 
     * @param keyword 搜索关键词
     * @return 符合条件的采购商数量
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM canton_fair_buyers WHERE telephone IS NOT NULL AND telephone != '' " +
            "AND (company_name LIKE CONCAT('%',#{keyword},'%') " +
            "OR contact_person LIKE CONCAT('%',#{keyword},'%'))" +
            "</script>")
    long countBuyersWithPhone(@Param("keyword") String keyword);
    
    /**
     * 根据邮箱精确查询广交会采购商
     * 
     * @param email 邮箱地址
     * @return 匹配的广交会采购商列表
     */
    @Select("SELECT id, company_name, contact_person, product_category, country, address, telephone, fax, website, " +
            " email, zip_code, `session` " +
            "FROM canton_fair_buyers WHERE email = #{email} LIMIT 10")
    List<BaseCantonFairBuyer> findByEmail(@Param("email") String email);

    /**
     * 根据电话号码精确查询广交会采购商
     *
     * @param phone 电话号码
     * @return 匹配的广交会采购商列表
     */
    @Select("SELECT id, company_name, contact_person, product_category, country, address, telephone, fax, website," +
            " email, zip_code, `session` FROM canton_fair_buyers WHERE telephone = #{phone} LIMIT 10")
    List<BaseCantonFairBuyer> findByPhone(@Param("phone") String phone);

    /**
     * 批量搜索：带手机号的采购商数据
     * SQL定义在CantonFairBuyerMapper.xml中
     */
    List<BaseCantonFairBuyer> searchBuyerDataWithPhone(@Param("companyName") String companyName, @Param("fullName") String fullName, @Param("website") String website);

    /**
     * 批量搜索：带邮箱的采购商数据
     * SQL定义在CantonFairBuyerMapper.xml中
     */
    List<BaseCantonFairBuyer> searchBuyerDataWithEmail(@Param("companyName") String companyName, @Param("fullName") String fullName, @Param("website") String website);

    /**
     * 批量邮件反查：根据邮箱查询采购商
     * SQL定义在CantonFairBuyerMapper.xml中
     */
    List<BaseCantonFairBuyer> searchBuyersByEmail(@Param("email") String email,
                                                  @Param("offset") int offset,
                                                  @Param("limit") int limit);

    /**
     * 批量邮件反查：根据电话查询采购商
     * SQL定义在CantonFairBuyerMapper.xml中
     */
    List<BaseCantonFairBuyer> searchBuyersByPhone(@Param("phone") String phone,
                                                  @Param("offset") int offset,
                                                  @Param("limit") int limit);
}